﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Threading.Tasks;

namespace DigDes.DSchool.SUPS.DataAccess.Database
{
    public class CargoDAC : BaseDAC
    {
        public static IEnumerable<DigDes.DSchool.SUPS.DataAccess.Interface.CargoDTO> GetCargoInformation()
        {
            using (var sqlConn = new SqlConnection(connectionString))
            using (var sqlCmd = sqlConn.CreateCommand())
            {
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.CommandText = "GetCargoInformation";
                sqlConn.Open();

                using (SqlDataReader reader = sqlCmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        DigDes.DSchool.SUPS.DataAccess.Interface.CargoDTO row = new DigDes.DSchool.SUPS.DataAccess.Interface.CargoDTO();

                        row.CargoName = ConvertFromDBDataToString(reader["Cargo_Name"]);
                        row.Description = ConvertFromDBDataToString(reader["Cargo_Description"]);
                        row.Code = ConvertFromDBDataToString(reader["Code"]);
                        row.CodeETSNG = ConvertFromDBDataToString(reader["CodeETSNG"]);
                        row.CargoETSNGName = ConvertFromDBDataToString(reader["CargoETSNG_Name"]);
                        row.CodeGNG = ConvertFromDBDataToString(reader["CodeGNG"]);
                        row.CodeGNGName = ConvertFromDBDataToString(reader["CargoGNG_Name"]);
                        row.Mnemocode = ConvertFromDBDataToString(reader["Mnemocode"]);

                        yield return row;
                    }
                }
            }
        }

        public static IEnumerable<DigDes.DSchool.SUPS.DataAccess.Interface.CargoDTO> GetCargoCatalogPage(int startRowIndex, int maximumRows)
        {
            using (var sqlConn = new SqlConnection(connectionString))
            using (var sqlCmd = sqlConn.CreateCommand())
            {
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.CommandText = "GetCargoCatalogPage";
                sqlCmd.Parameters.Add(new SqlParameter("@RowIndex", startRowIndex));
                sqlCmd.Parameters.Add(new SqlParameter("@MaxRows", maximumRows));
                sqlConn.Open();

                using (SqlDataReader reader = sqlCmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        DigDes.DSchool.SUPS.DataAccess.Interface.CargoDTO row = new DigDes.DSchool.SUPS.DataAccess.Interface.CargoDTO();

                        row.CargoID = ConvertFromDBDataToString(reader["ID"]);
                        row.CargoName = ConvertFromDBDataToString(reader["Cargo_Name"]);
                        row.Description = ConvertFromDBDataToString(reader["Cargo_Description"]);
                        row.Code = ConvertFromDBDataToString(reader["Code"]);
                        row.CodeETSNG = ConvertFromDBDataToString(reader["CodeETSNG"]);
                        row.CargoETSNGName = ConvertFromDBDataToString(reader["CargoETSNG_Name"]);
                        row.CodeGNG = ConvertFromDBDataToString(reader["CodeGNG"]);
                        row.CodeGNGName = ConvertFromDBDataToString(reader["CargoGNG_Name"]);
                        row.Mnemocode = ConvertFromDBDataToString(reader["Mnemocode"]);

                        yield return row;
                    }
                }
            }
        }

        public static DigDes.DSchool.SUPS.DataAccess.Interface.CargoDTO GetCargoByCode(string Code)
        {
            using (var sqlConn = new SqlConnection(connectionString))
            using (var sqlCmd = sqlConn.CreateCommand())
            {
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.CommandText = "GetCargoByCode";
                sqlCmd.Parameters.Add(new SqlParameter("@CodeParameter", Code));
                sqlConn.Open();

                using (SqlDataReader reader = sqlCmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        DigDes.DSchool.SUPS.DataAccess.Interface.CargoDTO row = new DigDes.DSchool.SUPS.DataAccess.Interface.CargoDTO();

                        row.CargoName = ConvertFromDBDataToString(reader["Cargo_Name"]);
                        row.Description = ConvertFromDBDataToString(reader["Cargo_Description"]);
                        row.Code = ConvertFromDBDataToString(reader["Code"]);
                        row.CodeETSNG = ConvertFromDBDataToString(reader["CodeETSNG"]);
                        row.CargoETSNGName = ConvertFromDBDataToString(reader["CargoETSNG_Name"]);
                        row.CodeGNG = ConvertFromDBDataToString(reader["CodeGNG"]);
                        row.CodeGNGName = ConvertFromDBDataToString(reader["CargoGNG_Name"]);
                        row.Mnemocode = ConvertFromDBDataToString(reader["Mnemocode"]);

                        return row;
                    }
                }
            }
            return new DigDes.DSchool.SUPS.DataAccess.Interface.CargoDTO();
        }

        public static void InsertCargo(string CargoName, string Description, string Code, string CodeETSNG, string CargoETSNGName, string CodeGNG, string CodeGNGName, string Mnemocode)
        {
             
            using (var sqlConn = new SqlConnection(connectionString))
            using (var sqlCmd = sqlConn.CreateCommand())
            {
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.CommandText = "InsertCargo";

                List<SqlParameter> parameters = new List<SqlParameter>();

                parameters.Add(new SqlParameter("@CargoName", CargoName));
                parameters.Add(new SqlParameter("@Description", Description));
                parameters.Add(new SqlParameter("@Code", Code));
                parameters.Add(new SqlParameter("@CodeETSNG", CodeETSNG));
                parameters.Add(new SqlParameter("@CargoETSNGName", CargoETSNGName));
                parameters.Add(new SqlParameter("@CodeGNG", CodeGNG));
                parameters.Add(new SqlParameter("@CodeGNGName", CodeGNGName));
                parameters.Add(new SqlParameter("@Mnemocode", Mnemocode));

                sqlCmd.Parameters.AddRange(parameters.ToArray());
                sqlConn.Open();
                sqlCmd.ExecuteNonQuery();
            }
        }

        public static void UploadImageByName(string ImageName, byte[] Image)
        {
            using (var sqlConn = new SqlConnection(connectionString))
            using (var sqlCmd = sqlConn.CreateCommand())
            {
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.CommandText = "InsertImageByName";

                sqlCmd.Parameters.Add("@ImageName", SqlDbType.NVarChar).Value = ImageName;
                sqlCmd.Parameters.Add("@Image", SqlDbType.VarBinary).Value = Image;

                sqlConn.Open();
                sqlCmd.ExecuteNonQuery();
            }
        }

        public static byte[] GetImageByID(string CargoID)
        {
            using (var sqlConn = new SqlConnection(connectionString))
            using (var sqlCmd = sqlConn.CreateCommand())
            {
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.CommandText = "GetImageByID";

                SqlParameter ImageID = new SqlParameter("@ImageID", System.Data.SqlDbType.Int);
                ImageID.Value = CargoID;
                sqlCmd.Parameters.Add(ImageID);

                sqlConn.Open();
                return ConvertFromDBDataToByteArray(sqlCmd.ExecuteScalar());
            }
        }

        public static void DeleteCargo(string CodeGNG)
        {
            using (var sqlConn = new SqlConnection(connectionString))
            using (var sqlCmd = sqlConn.CreateCommand())
            {
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.CommandText = "DeleteCargo";
                sqlCmd.Parameters.Add(new SqlParameter("@CodeGNG", CodeGNG));
                sqlConn.Open();
                sqlCmd.ExecuteNonQuery();
            }
        }

        public static void UpdateCargo(string CargoName, string Description, string Code, string CodeETSNG, string CargoETSNGName, string CodeGNG, string CodeGNGName, string Mnemocode)
        {
            using (var sqlConn = new SqlConnection(connectionString))
            using (var sqlCmd = sqlConn.CreateCommand())
            {
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.CommandText = "UpdateCargo";

                List<SqlParameter> parameters = new List<SqlParameter>();

                parameters.Add(new SqlParameter("@CargoName", CargoName));
                parameters.Add(new SqlParameter("@Description", Description));
                parameters.Add(new SqlParameter("@Code", Code));
                parameters.Add(new SqlParameter("@CodeETSNG", CodeETSNG));
                parameters.Add(new SqlParameter("@CargoETSNGName", CargoETSNGName));
                parameters.Add(new SqlParameter("@CodeGNG", CodeGNG));
                parameters.Add(new SqlParameter("@CodeGNGName", CodeGNGName));
                parameters.Add(new SqlParameter("@Mnemocode", Mnemocode));

                sqlCmd.Parameters.AddRange(parameters.ToArray());
                sqlConn.Open();
                sqlCmd.ExecuteNonQuery();
            }
        }

        public static int GetCargoAmount(int startRowIndex, int maximumRows)
        {
            using (var sqlConn = new SqlConnection(connectionString))
            using (var sqlCmd = sqlConn.CreateCommand())
            {
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.CommandText = "GetCargoAmount";
                sqlConn.Open();
                using (SqlDataReader reader = sqlCmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int tmp = (int)reader[0];
                        return tmp;
                    }
                }
                sqlCmd.ExecuteNonQuery();
            }
            return 0;
        }
    }
}
